﻿//********************************************************************************//
//创建日期：<创建日期，2017-10-27>
//创建作者：<孙雪婷，sunxueting@shanghai3h.com>
//功能说明：超计划通知单
//********************************************************************************//

using System;
using System.Data;
using System.IO;
using BLL;
using BLL.Access;
using DevExpress.Web;
using Entity;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Web.Class;

namespace Web.Pages.YongShui.BaoBiaoGL.DanJuDY
{
    public partial class ChaoJiHTZD : BasePage
    {
        #region 属性

        /// <summary>
        ///     保存结果集1
        /// </summary>
        private DataTable dtGrid
        {
            get { return ViewState["dtGrid"] as DataTable; }
            set { ViewState["dtGrid"] = value; }
        }

        #endregion

        #region 变量

        /// <summary>
        ///     用户信息
        /// </summary>
        private YS_YG_YONGHUXX _YS_YG_YONGHUXX = new YS_YG_YONGHUXX();

        /// <summary>
        ///     用户信息bll
        /// </summary>
        private YS_YG_YONGHUXXBLL _YS_YG_YONGHUXXBLL = new YS_YG_YONGHUXXBLL();

        /// <summary>
        ///     管理部门
        /// </summary>
        private YS_JX_ZHANDIAN _YS_JX_ZHANDIAN = new YS_JX_ZHANDIAN();

        /// <summary>
        ///     管理部门bll
        /// </summary>
        private YS_JX_ZHANDIANBLL _YS_JX_ZHANDIANBLL = new YS_JX_ZHANDIANBLL();

        /// <summary>
        ///     管理块
        /// </summary>
        private YS_JX_GUANLIK _YS_JX_GUANLIK = new YS_JX_GUANLIK();

        /// <summary>
        ///     管理块BLL
        /// </summary>
        private readonly YS_JX_GUANLIKBLL _YS_JX_GUANLIKBLL = new YS_JX_GUANLIKBLL();


        /// <summary>
        ///     存储过程调用类
        /// </summary>
        private readonly SpHelper _SpHelper = new SpHelper();

        #endregion

        #region 事件

        /// <summary>
        ///     页面加载事件
        /// </summary>
        protected void Page_Load(object sender, EventArgs e)
        {
            //第一次页面加载
            if (!IsPostBack)
                InitData();
            ScriptManager1.RegisterPostBackControl(btnExport);
            gvYongHuXX.DataSource = dtGrid;
            gvYongHuXX.DataBind();
        }


        /// <summary>
        ///     查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnChaXun_Click(object sender, EventArgs e)
        {
            if (txtShuiLiangYM.Text != "")
                LoadGridData();
        }


        /// <summary>
        ///     tab1导出excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
        }

        private void ExportExcel(DataTable dt)
        {
            var wb = new HSSFWorkbook();
            var sheet = (HSSFSheet) wb.CreateSheet("Sheet1"); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            var rowTitle = (HSSFRow) sheet.CreateRow(0); //创建列头行
            var columns = new[]
            {
                "业务管理部门", "电话", "业务员", "用户邮编", "用户地址", "用户编号", "用户名", "年", "月", "现计划用水量", "实际用量", "超计划用量", "超计划金额"
            };
            for (var i = 0; i < columns.Length; i++)
            {
                var cellTitle = (HSSFCell) rowTitle.CreateCell(i);
                cellTitle.SetCellValue(columns[i]);
            }
            var index = 1;
            foreach (DataRow row in dt.Rows)
            {
                var rowData = (HSSFRow) sheet.CreateRow(index);
                for (var i = 0; i < row.ItemArray.Length; i++)
                {
                    var cellData = (HSSFCell)rowData.CreateCell(i);
                    switch (i)
                    {
                        case 0: cellData.SetCellValue(row["S_BUMEN"].ToString()); break;
                        case 1: cellData.SetCellValue(row["S_DIANHUA"].ToString()); break;
                        case 2: cellData.SetCellValue(row["USERNAME"].ToString()); break;
                        case 3: cellData.SetCellValue(row["S_YOUBIAN"].ToString()); break;
                        case 4: cellData.SetCellValue(row["S_DIZHI"].ToString()); break;
                        case 5: cellData.SetCellValue(row["S_YONGHUH"].ToString()); break;
                        case 6: cellData.SetCellValue(row["S_YONGHUM"].ToString()); break;
                        case 7: cellData.SetCellValue(row["I_Y"].ToString()); break;
                        case 8: cellData.SetCellValue(row["I_M"].ToString()); break;
                        //case 9: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_YUANJIHUA"])); break;
                        //case 10: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_SHIYONGSL"])); break;
                        //case 11: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_CHAOJIHSL"])); break;
                        //case 12: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["N_CHAOJIHJE"])); break;
                        case 9: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_SHIYONGJH"])); break;
                        case 10: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_SHIYONGSL"])); break;
                        case 11: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["I_JIAJIAL"])); break;
                        case 12: cellData.SetCellType(CellType.Numeric); cellData.SetCellValue(Convert.ToDouble(row["N_JIAJIAJE"])); break;
                    }

                }
                index++;
            }
            var fileName = string.Format("{0}告知书（{1}{2}笔{3:yyyyMMdd}邮）.xls", txtShuiLiangYM.Text, cmbTZ1.SelectedItem.Text,
                dt.Rows.Count, DateTime.Now);
            var stream = new MemoryStream();
            wb.Write(stream);
            var buf = stream.ToArray();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
            Response.BinaryWrite(buf);
        }

        #endregion

        #region 方法

        /// <summary>
        ///     初始化数据
        /// </summary>
        private void InitData()
        {
            gvYongHuXX.SettingsPager.Mode = GridViewPagerMode.EndlessPaging;
            //水量年月
            var water_y_m = BusinessTool.YS_GETSETTINGSVALUE(eSETTINGS_KEYNAME.WATER_Y_M.ToString());

            txtShuiLiangYM.Text = water_y_m;

            //int userid = LoginInfo.CurrentUser.UserID;
            //string username = LoginInfo.CurrentUser.UserName;
            ////根据操作人加载管理部门(站点)
            //DataTable dtZhanDian = BusinessTool.GetTextAndValue("View_AS_UserInfo", "ZHANDIANBH", "ROLEID", null, string.Format("USERID={0}", userid)).Tables[0];
            //string zhandian = dtZhanDian.Rows[0]["TEXT"].ToString();//站点
            //string roleid = dtZhanDian.Rows[0]["VALUE"].ToString();//9001 业务员，3001 办事处主任

            //管理块 string.Format("S_ZHANDIAN={0}", "00")
            var dtGuanLiK = _YS_JX_GUANLIKBLL.SelectYS_JX_GUANLIKsDynamic("i_jlzt=0", "S_GUANLIKH");
            cmbGuanLiK.Items.Add("全部", "0");
            for (var i = 0; i < dtGuanLiK.Rows.Count; i++)
                cmbGuanLiK.Items.Add(dtGuanLiK.Rows[i]["S_GUANLIKMC"].ToString(),
                    dtGuanLiK.Rows[i]["S_GUANLIKH"].ToString());
            cmbGuanLiK.SelectedIndex = 0;
        }


        /// <summary>
        ///     加载查询数据
        /// </summary>
        private void LoadGridData()
        {
            var v_qk = cmbGuanLiK.Value.ToString();
            var v_y = Convert.ToInt32(txtShuiLiangYM.Text) / 100;
            var v_m = Convert.ToInt32(txtShuiLiangYM.Text) % 100;
            var ts = cmbTZ1.Text != "" ? cmbTZ1.Value.ToString() : null;
            var tz = cmbTZ2.Text != "" ? cmbTZ2.Value.ToString() : null;


            dtGrid = _SpHelper.P_YSBB_CJHTZD(v_y, v_m, v_qk, ts, tz).Tables[0];
            gvYongHuXX.DataSource = dtGrid;
            gvYongHuXX.DataBind();
            if (dtGrid != null && dtGrid.Rows.Count > 0)
            {
                zjl.Text = "共" + dtGrid.Rows.Count + "条";
                gvYongHuXX.FocusedRowIndex = 0;
            }
        }


        /// <summary>
        ///     根据用户号定位
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDingWei_Click(object sender, EventArgs e)
        {
            if (txtYongHuH.Text.Trim() != "")
            {
                var id = PageTools.FocusOpreateRecord(dtGrid, gvYongHuXX, "S_YONGHUH", txtYongHuH.Text, true);
                if (id == -1)
                    PageTools.AlertMsg(this, "当前数据中没有你要找的数据,请检查你输入的条件是否正确。");
            }
        }


        protected void btnExport_Click(object sender, EventArgs e)
        {
            if (dtGrid == null || dtGrid.Rows.Count < 1)
                PageTools.AlertMsg(this, "没有符合的数据导出");
            else
                ExportExcel(dtGrid);
        }

        #endregion
    }
}